<?php

namespace App\Command\ResumeParsing\Service\ParsingSpecial;

use EchoPHP\Facades\Log as Log;
use PhpOffice\PhpSpreadsheet;

class ParsingSpecialExcel extends ParsingSpecial {
    /**
     * @var PhpSpreadsheet\Worksheet\Worksheet
     */
    protected $sheet;

    protected $titlePatternTemplate = ['range' => 'A1', 'core' => 'A1', 'xfIndex' => null];
    protected $titlePattern = ['range' => null, 'core' => null, 'xfIndex' => null];

    //自定义初始化数据逻辑，可以被重载
    protected $data = [];

    protected function getTitlePattern() {
        $this->titlePattern = $this->titlePatternTemplate;
        if (is_string($this->titlePattern['range'])) $this->titlePattern['range'] = PhpSpreadsheet\Cell\Coordinate::rangeBoundaries($this->titlePattern['range']);
        if (is_string($this->titlePattern['core'])) $this->titlePattern['core'] = PhpSpreadsheet\Cell\Coordinate::rangeBoundaries($this->titlePattern['core'])[0];
        if (empty($xfIndex = $this->matchTitleStyle())) return false;
        $this->titlePattern['xfIndex'] = $xfIndex;
        return $this->titlePattern;
    }

    /**
     * 依据标题样式，分块拆分工作表数据
     * @param int $rowStep 按行步进解析的步长，与$columnStep互斥
     * @param int $columnStep 按列步进解析的步长，若指定$rowStep则此项无效
     */
    protected function explode(int $rowStep = 1, int $columnStep = 0) {
        if ($rowStep > 0) $columnStep = 0;
        elseif ($rowStep <= 0 && $columnStep <= 0) throw new \InvalidArgumentException('Either $rowStep or $columnStep must be positive.');
        $titleArr = [];
        try {
            $maxRow = $this->sheet->getHighestRow();
            $maxColumn = PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($this->sheet->getHighestColumn());

            for ($row = $this->titlePattern['core'][1], $column = $this->titlePattern['core'][0]; $row <= $maxRow && $column <= $maxColumn; $row += $rowStep, $column += $columnStep) {
                if ($title = $this->getTitle($row, $column)) $titleArr[] = ['text' => $title, 'row' => $row, 'column' => $column];
            }
            $titleArr[] = ['row' => $row, 'column' => $column];
            if ($titleArr[0]['row'] != $this->titlePattern['core'][1])
                array_unshift($titleArr, ['text' => 'misc', 'row' => $this->titlePattern['core'][1] - 1, 'column' => $this->titlePattern['core'][0]]);
            $offsetBegin = $rowStep > 0 ? [$this->titlePattern['range'][0][0] - $this->titlePattern['core'][0], $this->titlePattern['range'][1][1] + 1 - $this->titlePattern['core'][1]] :
                [$this->titlePattern['range'][1][0] + 1 - $this->titlePattern['core'][0], $this->titlePattern['range'][0][1] - $this->titlePattern['core'][1]];
            $offsetBeforeNext = $rowStep > 0 ? [$this->titlePattern['range'][1][0] - $this->titlePattern['core'][0], $this->titlePattern['range'][0][1] - 1 - $this->titlePattern['core'][1]] :
                [$this->titlePattern['range'][0][0] - 1 - $this->titlePattern['core'][0], $this->titlePattern['range'][1][1] - $this->titlePattern['core'][1]];
            $this->data = [];
            for ($i = 0; $i < count($titleArr) - 1; $i++) {
                $part = $this->sheet->rangeToArray($this->range([
                    [$titleArr[$i]['column'] + $offsetBegin[0], $titleArr[$i]['row'] + $offsetBegin[1]],
                    [$titleArr[$i + 1]['column'] + $offsetBeforeNext[0], $titleArr[$i + 1]['row'] + $offsetBeforeNext[1]]
                ]));
                $this->data[$titleArr[$i]['text']] = $part;
            }
        } catch (\Exception $e) {
            Log::error(e_dump($e), ['sheetTitle' => $this->sheet->getTitle(), 'data' => $this->data], 'cmd');
        }
    }

    /**
     * 获取指定位置的标题文字
     * @param int $rowIndex 行号（从1开始的数字），对应标题模板区域的core单元格坐标；默认null则对齐标题模板区域
     * @param int $columnIndex 列号（从1开始的数字），对应标题模板区域的core单元格坐标；默认null则对齐标题模板区域
     * @return bool|mixed|null 若指定位置符合标题样式，则返回标题文字；不符合返回false；超出有效区域返回null
     */
    function getTitle(int $rowIndex = null, int $columnIndex = null) {
        if (empty($rowIndex)) $rowIndex = $this->titlePattern['core'][1];
        if (empty($columnIndex)) $columnIndex = $this->titlePattern['core'][0];
        $rowBegin = $rowIndex + $this->titlePattern['range'][0][1] - $this->titlePattern['core'][1];
        $columnBegin = $columnIndex + $this->titlePattern['range'][0][0] - $this->titlePattern['core'][0];
        $rowEnd = $rowIndex + $this->titlePattern['range'][1][1] - $this->titlePattern['core'][1];
        $columnEnd = $columnIndex + $this->titlePattern['range'][1][0] - $this->titlePattern['core'][0];
        for ($row = $rowBegin; $row <= $rowEnd; $row++) {
            for ($column = $columnBegin; $column <= $columnEnd; $column++) {
                if (empty($cell = $this->sheet->getCellByColumnAndRow($column, $row, false))) return null;
                if ($cell->getXfIndex() != $this->titlePattern['xfIndex']) return false;
            }
        }
        if (empty($cell = $this->sheet->getCellByColumnAndRow($columnIndex, $rowIndex, false))) return null;
        return $cell->getValue();
    }

    protected function pairRowData($data, $keyColumn = 0, $valueColumn = 1) {
        $named = [];
        $misc = [];
        foreach ($data as $row) {
            if (empty($row[$valueColumn])) continue;
            if (!empty($row[$keyColumn])) {
                $named[$row[$keyColumn]] = $row[$valueColumn];
            } else {
                $misc[] = $row[$valueColumn];
            }
        }
        if (!empty($misc)) $named['misc'] = $misc;
        return $named;
    }

    protected function assignColumnData($data, $titleRow = 0) {
        $result = [];
        $titleMap = $data[$titleRow] ?? [];
        foreach ($data as $rowIndex => $row) {
            if ($rowIndex == $titleRow) continue;
            $item = [];
            foreach ($row as $columnIndex => $value) {
                if (empty($value) && empty($titleMap[$columnIndex])) continue;
                $item[empty($titleMap[$columnIndex]) ? $columnIndex : $titleMap[$columnIndex]] = $value ?? '';
            }
            $result[] = $item;
        }
        return $result;
    }

    protected function onParseInit(...$params) {
        if (count($params) < 1) throw new \ArgumentCountError('PhpSpreadsheet\Worksheet\Worksheet $sheet expected, none given');
        if (!$params[0] instanceof PhpSpreadsheet\Worksheet\Worksheet) throw new \InvalidArgumentException('PhpSpreadsheet\Worksheet\Worksheet $sheet expected');
        $this->sheet = $params[0];
        $this->data = [];
    }

    //自定义parse后行为，可以被重载
    protected function onParsed() {
    }

    //检测简历是否适用当前规则集，可以被重载自定义检测方式
    protected function test() {
        if (!$this->getTitlePattern()) return false;
        return true;
    }

    protected function matchTitleStyle() {
        return false;
    }

    protected function cell($cellCoordinateArr) {
        if (count($cellCoordinateArr) != 2) throw new \InvalidArgumentException('$cellCoordinateArr should be [Column Number, Row Number].');
        if (!is_numeric($row = $cellCoordinateArr[1])) throw new \InvalidArgumentException('Invalid row number.');
        $column = is_numeric($cellCoordinateArr[0]) ? PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($cellCoordinateArr[0]) : $cellCoordinateArr[0];
        if (!is_string($column)) throw new \InvalidArgumentException('Invalid column number.');
        return $column . $row;
    }

    protected function range($rangeCoordinateArr) {
        if (count($rangeCoordinateArr) != 2) throw new \InvalidArgumentException('$rangeCoordinateArr should be [Start Cell, End Cell].');
        $result = $this->cell($rangeCoordinateArr[0]);
        if ($rangeCoordinateArr[1] != $rangeCoordinateArr[0]) $result .= ':' . $this->cell($rangeCoordinateArr[1]);
        return $result;
    }

    protected function groupFormatCallback($item) {
        if (is_array($item)) $item = implode("\r\n", $item);
        return parent::groupFormatCallback($item);
    }

    protected function getLinkTargetSheet($link, PhpSpreadsheet\Spreadsheet $workbook = null) {
        try {
            if ($link instanceof PhpSpreadsheet\Cell\Cell) {
                $workbook = $link->getParent()->getParent()->getParent();
                $link = $link->getHyperlink();
            }
            if (empty($workbook)) return null;
            if (!$link instanceof PhpSpreadsheet\Cell\Hyperlink) return null;
            if (!$link->isInternal()) return null;
            if (!preg_match('/^sheet:\/\/(?:\'(?<sheetNameQ>.+)\'|(?<sheetName>.+?))(?:!(?<coordinate>[A-Za-z]+\d+))?$/u', $link->getUrl(), $matches)) return null;

            $sheetName = !empty($matches['sheetNameQ']) ? $matches['sheetNameQ'] : $matches['sheetName'];
            $sheetName = preg_replace("/''/u", "'", $sheetName);
            return $workbook->getSheetByName($sheetName);
        } catch (\Exception $e) {
            $info = [];
            if ($link instanceof PhpSpreadsheet\Cell\Cell) {
                $info['sheetName'] = $link->getParent()->getParent()->getTitle();
                $info['coordinate'] = $link->getCoordinate();
            } elseif ($link instanceof PhpSpreadsheet\Cell\Hyperlink) {
                $info['linkUrl'] = $link->getUrl();
            }
            Log::error(e_dump($e), $info, 'cmd');
            return null;
        }
    }

    protected function fillInWorksheet(array $data, PhpSpreadsheet\Worksheet\Worksheet $sheet, $range, int $keyColumn, int $valueColumn) {
        if (is_string($range)) $range = PhpSpreadsheet\Cell\Coordinate::rangeBoundaries($range);
        $oldValues = [];
        if (!is_array($range) || !isset($range[0][1]) || !is_numeric($range[0][1]) || !isset($range[1][1]) || !is_numeric($range[1][1]) || !isset($range[0][0]) || !is_numeric($range[0][0])) return $oldValues;
        $keyColumn += $range[0][0] - 1;
        $valueColumn += $range[0][0] - 1;
        for ($row = $range[0][1]; count($data) && $row <= $range[1][1]; $row++) {
            try {
                if (empty($keyCell = $sheet->getCellByColumnAndRow($keyColumn, $row))) continue;
                if (empty($key = $keyCell->getValue())) continue;
                if (!empty($data[$key])) {
                    $valueCell = $sheet->getCellByColumnAndRow($valueColumn, $row);
                    $oldValues[$key] = $valueCell->getValue();
                    if (is_numeric($data[$key])) {
                        $valueCell->setValueExplicit($data[$key], PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                    } else {
                        $valueCell->setValue($data[$key]);
                        if (preg_match('/^\S+@\S+\.\S+$/u', $data[$key])) {
                            $valueCell->getHyperlink()->setUrl('mailto:' . $data[$key]);
                        } elseif (preg_match('/^https?:\/\/[-\w]+(?:\.[-\w]+)+(?:\/\S*)?$/u', $data[$key])) {
                            $valueCell->getHyperlink()->setUrl($data[$key]);
                        }
//                        $valueCell->getStyle()->applyFromArray([     //TODO:超链接样式
//                            'font' => [
//                                'color' => ['rgb' => '0000FF'],
//                                'underline' => 'single'
//                            ]
//                        ]);
                    }
                }
            } catch (\Exception $e) {
                Log::error(e_dump($e), ['sheetName' => $sheet->getTitle(), 'row' => $row], 'cmd');
            } finally {
                if (!empty($key)) unset($data[$key]);
            }
        }
        return $oldValues;
    }
}